from fastapi import FastAPI, Request
from fastapi.templating import Jinja2Templates
from pydantic import BaseModel
import pandas as pd
templates = Jinja2Templates(directory="templates") 

app = FastAPI()
import time
from typing import List

from fastapi import Depends, FastAPI, HTTPException

import pymysql
#html
class TextArea(BaseModel):
    country: str
    IPC:str
    gkn:int
    pub:int

class TextArea1(BaseModel):
    country: str
    IPC:str
    gkn:list
    pub:int 

class TextArea2(BaseModel):
    country: str
    IPC:str
    gkn:int
    pub:list 

@app.get("/")
async def serve_home(request: Request):
    return templates.TemplateResponse("home.html", {"request": request})

@app.post("/search")
async def search(data:TextArea):
    print(data)
    conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="12345678",
                       db="database2", local_infile=1, use_unicode=True, charset="utf8")
    cursor = conn.cursor()
    post_dict=data.dict()
    country=post_dict['country'].lower()
    IPC=post_dict['IPC'].upper()
    sql1 = 'select gkn_count,count,avg_count from {} where IPC={} and gkn={} and pub_year={}'.format(country+"_pic",
                '"'+IPC+'"', post_dict['gkn'],post_dict['pub'])
    df1 = pd.read_sql(sql1, con=conn)
    gkn_count=int(df1['gkn_count'][0])
    count=int(df1['count'][0])
    avg_count=df1['avg_count'][0]
    cursor.close()
    conn.close()
    return {"gkn_count":gkn_count,"count":count,"avg_count":avg_count}



@app.post("/search_list")
async def search_list(data:TextArea1):
    print(data)
    conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="12345678",
                       db="database2", local_infile=1, use_unicode=True, charset="utf8")
    cursor = conn.cursor()
    post_dict=data.dict()
    country=post_dict['country'].lower()
    IPC=post_dict['IPC'].upper()
    sql1 = 'select avg_count from {} where IPC={} and pub_year={} and gkn>={} and gkn<={}'.format(country+"_pic",
                '"'+IPC+'"',post_dict['pub'], post_dict['gkn'][0], post_dict['gkn'][1])
    df1 = pd.read_sql(sql1, con=conn)
    # gkn_count=int(df1['gkn_count'][0])
    # count=int(df1['count'][0])
    avg_count=df1['avg_count']
    cursor.close()
    conn.close()
    
    return {"avg_count":avg_count}


@app.post("/search_list1")
async def search_list1(data:TextArea2):
    print(data)
    conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="12345678",
                       db="database2", local_infile=1, use_unicode=True, charset="utf8")
    cursor = conn.cursor()
    post_dict=data.dict()
    country=post_dict['country'].lower()
    IPC=post_dict['IPC'].upper()
    sql1 = 'select avg_count from {} where IPC={} and gkn={} and pub_year>={} and pub_year<={}'.format(country+"_pic",
                '"'+IPC+'"',post_dict['gkn'], post_dict['pub'][0], post_dict['pub'][1])
    df1 = pd.read_sql(sql1, con=conn)
    # gkn_count=int(df1['gkn_count'][0])
    # count=int(df1['count'][0])
    avg_count=df1['avg_count']
    cursor.close()
    conn.close()
    
    return {"avg_count":avg_count}



# @app.post("/search")
# async def search(data:TextArea2):
#     conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="12345678",
#                        db="database2", local_infile=1, use_unicode=True, charset="utf8")
#     cursor = conn.cursor()
#     post_dict=data.dict()
#     country=post_dict['country'].lower()
#     IPC=post_dict['IPC'].upper()
#     sql1 = 'select avg_count from {} where IPC={} and pub_year>={} and pub_year<={} and gkn={} '.format(country+"_pic",
#                 '"'+IPC+'"',post_dict['pub'][0], post_dict['pub'][1],post_dict['gkn'])
#     df1 = pd.read_sql(sql1, con=conn)
#     gkn_count=int(df1['gkn_count'][0])
#     count=int(df1['count'][0])
#     avg_count=df1['avg_count'][0]
#     cursor.close()
#     conn.close()
#     return {"avg_count":avg_count}

@app.post("/search_list")
async def search_list(data:TextArea):
    return {"avg_count":{1980:1,1981:2,1982:3,1983:4,1984:5}}
